SELECT t.code
FROM c1_permission t
WHERE EXISTS
(SELECT 1 FROM c1_site e WHERE e.id = t.site_id AND e.code = :siteCode
)
AND EXISTS
(SELECT 1
FROM c1_dictionary e
WHERE e.id = t.type_id
AND e.code = 'authorityUrls'
)
AND (EXISTS
(SELECT 1
FROM c1_role_permission e
JOIN c1_role_user e_role_user
ON (e_role_user.role_id = e.role_id)
WHERE e.permission_id   = t.id
AND e_role_user.id      = :userId
)
OR EXISTS
(SELECT 1
FROM c1_role_permission e
JOIN c1_us_group_role t_us_group_role
ON (t_us_group_role.role_id = e.role_id)
JOIN c1_us_group_user t_us_group_user
ON (t_us_group_user.user_group_id = t_us_group_role.user_group_id)
WHERE e.permission_id             = t.id
AND t_us_group_user.user_id       = :userId
) )
ORDER BY t.order_num,
LENGTH(t.code) DESC
